package cnedu.ncist.jobhard.service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import edu.ncist.wang.hkdf.bussiness.blo.AbstractService;
import edu.ncist.wang.hkdf.dao.database.DBConnectionException;
import edu.ncist.wang.hkdf.dao.database.DataBaseAccess;
import edu.ncist.wang.hkdf.dao.database.DataManipulationException;

public class JobhardService extends AbstractService {

	public Map<String, Object> doServiceProcess(Map paraMap){
		// TODO Auto-generated method stub
		String actionType = (String)paraMap.get("ACTIONTYPE");
		System.out.println("***********"+actionType);
		if("job_Hard_Overall".equals(actionType)){
			return job_hard_overall(paraMap);
		}
		if("job_Hard_Overall_Should".equals(actionType)){
			return job_hard_overall_should(paraMap);
		}
		if("job_Hard_Overall_Actual".equals(actionType)){
			return job_hard_overall_actual(paraMap);
		}
		if("job_Hard_Overall_Good_Job".equals(actionType)){
			return job_hard_overall_good_job(paraMap);
		}
		if("job_Hard_Overall_Bad_Job".equals(actionType)){
			return job_hard_overall_bad_job(paraMap);
		}
		if("job_Hard_Detail".equals(actionType)){
			return job_hard_detail(paraMap);
		}
		if("job_Hard_Data".equals(actionType)){
			return job_hard_basic_data(paraMap);
		}
		if("job_Hard_Data_Topic".equals(actionType)){
			return job_hard_data_topic(paraMap);
		}
	    if("job_Hard_Overall_Rate".equals(actionType)){
			return job_hard_overall_rate(paraMap);
		}
	    if("job_Hard_Overall_Usual_Rate".equals(actionType)){
			return job_hard_overall_usual_rate(paraMap);
		}
		 if("job_Hard_Overall_Fail_Rate".equals(actionType)){
			return job_hard_overall_fail_rate(paraMap);
		}
		else{
			ActionTypeError();
		}
		return null;
	}
	
	/*public Map<String, Object> newest_job(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  newestJobList = null;
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		sql="select job_id from job_is_hard   "+
				" where course_id='"+courseId+"'   and class_id='"+classId+"'   "+
				" order by job_id desc  limit 1";
		try{
			 newestJobList = dba.getMultiRecord(sql);
			 dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  newestJobList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("NEWESTJOBLIST",  newestJobList);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	*/
	
	/*public Map<String, Object> job_hard_overall(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  jobHardList = null;
		List<Map<String, String>>  compileAndRunResult = null;
		List<Map<String, String>> allJob = null;
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		System.out.println("hahahahahhahaha"+courseId);
		String classId=session.getAttribute("CLASSID").toString();
		 DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		String sql=null;
		String sql2=null;
		if(choice1==null || choice1==""){
			sql = "select topic_id,topic_name, "+
						"count(case when rate>=0.8 then student_id end) as success, "+
						"count(case when rate>=0.6 and rate<0.8 then student_id end) as pass, "+
						"count(case when rate<0.6 then student_id end) as nopass from	  "+
						"(select topic_id,student_id,topic_name,topic_total_value,final_scorer,final_scorer/topic_total_value as rate from job_is_hard,topic   "+
						"where job_is_hard.topic_id=topic.topic_only_id  "+
						"and course_id='"+courseId+"'    and class_id='"+classId+"' "+
						"  and job_id=(select job_id from job_is_hard where course_id='"+courseId+"'   and class_id='"+classId+"' order by job_id desc  limit 1 )   "+
						"ORDER BY  topic_id) temp group by topic_id";
		}
		else{
			sql = "select topic_id,topic_name, "+
					"count(case when rate>=0.8 then student_id end) as success, "+
					"count(case when rate>=0.6 and rate<0.8 then student_id end) as pass, "+
					"count(case when rate<0.6 then student_id end) as nopass from	  "+
					"(select topic_id,student_id,topic_name,topic_total_value,final_scorer,final_scorer/topic_total_value as rate from job_is_hard,topic   "+
					"where job_is_hard.topic_id=topic.topic_only_id  "+
					"and course_id='"+courseId+"' and job_id='"+choice1+"'   and class_id='"+classId+"'  "+
					"ORDER BY  topic_id) temp group by topic_id";
		}
		String  sql1 = "select job_id FROM job_is_hard where course_id='"+courseId+"'  GROUP BY job_id";
		if(choice1==null || choice1.equals("") ){
			sql2 = "select topic_id,topic_name,count(*) as counts, "+
						"count(case when compile_result=0 and run_result=0 then student_id end) as compile_fail,   "+
						"count(case when compile_result=1 and run_result=0 then student_id end) as run_fail,   "+
						"count(case when compile_result=1 and run_result=1 then student_id end) as success from	  "+
						"(select topic_id,student_id,topic_name,compile_result,run_result from job_is_hard,topic   "+
						"where job_is_hard.topic_id=topic.topic_only_id  "+
						"and course_id='"+courseId+"'   and class_id='"+classId+"' "+ 
						" and job_id=(select job_id from job_is_hard where course_id='"+courseId+"'   and class_id='"+classId+"' order by job_id desc  limit 1 )   "+
						"ORDER BY  topic_id) temp group by topic_id";
		}
		else{
			sql2 = "select topic_id,topic_name,count(*) as counts, "+
					"count(case when compile_result=0 and run_result=0 then student_id end) as compile_fail,   "+
					"count(case when compile_result=1 and run_result=0 then student_id end) as run_fail,   "+
					"count(case when compile_result=1 and run_result=1 then student_id end) as success from	  "+
					"(select topic_id,student_id,topic_name,compile_result,run_result from job_is_hard,topic   "+
					"where job_is_hard.topic_id=topic.topic_only_id  "+
					"and course_id='"+courseId+"'   and class_id='"+classId+"' "+ 
					" and job_id='"+choice1+"'  "+
					"ORDER BY  topic_id) temp group by topic_id";
		}
		try{
			 jobHardList = dba.getMultiRecord(sql);
			 allJob = dba.getMultiRecord(sql1);
			 compileAndRunResult  = dba.getMultiRecord(sql2);
			 dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  jobHardList || null== compileAndRunResult){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("JOBHARDSTATUS",  jobHardList);
		resultMap.put("COMPILEANDRUNRESULT", compileAndRunResult);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	*/
	
	public Map<String, Object> job_hard_overall(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  jobHardList = null;
		List<Map<String, String>>  compileAndRunResult = null;
		List<Map<String, String>> allJob = null;
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		 DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		String sql=null;
		if(choice1==null || choice1==""){
		 sql="select temp.topic_id,temp.topic_name,avgScorer,count(*) as actually_counts,should_counts,  "+
					" count(case when final_scorer=0  then student_id end) as badJob,    "+
					" count(case when final_scorer=topic_total_value then student_id end) as goodJob   "+
					" from "+  
					" (select topic_id,student_id,topic_name,final_scorer,topic_total_value from job_is_hard,topic   "+
					" where job_is_hard.topic_id=topic.topic_only_id   "+
					" and course_id='"+courseId+"'   and class_id='"+classId+"'    "+
					" and job_id=(select job_id from job_is_hard  where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)  "+
					"  ORDER BY  topic_id) temp,  "+
					" (select topic_id,topic_name,course_id,avg(final_scorer) as avgScorer   "+
					" from job_is_hard  "+
					" where  "+
					" course_id='"+courseId+"'  and class_id='"+classId+"'  "+
					"  and job_id=(select job_id from job_is_hard   where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)  "+
					"   GROUP BY topic_id) avg_temp,  "+
					"  (select chose_course_id,count(*) as should_counts    "+
					" from student_course where chose_course_id='"+courseId+"'    "+   
					"  and course_class_id='"+classId+"' GROUP BY chose_course_id) should_hand_over  "+
					"  where temp.topic_id=avg_temp.topic_id   "+
					"  and avg_temp.course_id=should_hand_over.chose_course_id   "+
					"  group by topic_id";
			
		}
		else{
			sql="select temp.topic_id,temp.topic_name,avgScorer,count(*) as actually_counts,should_counts,  "+
					" count(case when final_scorer=0  then student_id end) as badJob,    "+
					" count(case when final_scorer=topic_total_value then student_id end) as goodJob   "+
					" from "+  
					" (select topic_id,student_id,topic_name,final_scorer,topic_total_value from job_is_hard,topic   "+
					" where job_is_hard.topic_id=topic.topic_only_id   "+
					" and course_id='"+courseId+"'   and class_id='"+classId+"'    "+
					" and job_id='"+choice1+"'  "+
					"  ORDER BY  topic_id) temp,  "+
					" (select topic_id,topic_name,course_id,avg(final_scorer) as avgScorer   "+
					" from job_is_hard  "+
					" where  "+
					" course_id='"+courseId+"'  and class_id='"+classId+"'  "+
					"  and job_id='"+choice1+"'  "+
					"   GROUP BY topic_id) avg_temp,  "+
					"  (select chose_course_id,count(*) as should_counts    "+
					" from student_course where chose_course_id='"+courseId+"'    "+   
					"  and course_class_id='"+classId+"' GROUP BY chose_course_id) should_hand_over  "+
					"  where temp.topic_id=avg_temp.topic_id   "+
					"  and avg_temp.course_id=should_hand_over.chose_course_id   "+
					"  group by topic_id";
		}
		String  sql1 = "select job_id FROM job_is_hard where course_id='"+courseId+"'  and class_id='"+classId+"'GROUP BY job_id";
		System.out.println(sql1);
		
		try{
			 jobHardList = dba.getMultiRecord(sql);
			 allJob = dba.getMultiRecord(sql1);
			 dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  jobHardList || null== compileAndRunResult){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("JOBHARDSTATUS",  jobHardList);
		
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object> job_hard_overall_should(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		System.out.println("&&&&&&&&&&&&"+choice1);
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		System.out.println("hahahahahhahaha"+courseId);
		String classId=session.getAttribute("CLASSID").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		sql = "select student.student_name FROM  "+
					"  student,student_course  "+
					" where student.student_id = student_course.student_id  "+
					" and course_class_id='"+classId+"' and chose_course_id='"+courseId+"'   "+
					" GROUP BY student.student_id";
		
		System.out.println(sql);
		String  sql1 = "select job_id FROM job_is_hard where course_id='"+courseId+"' and class_id='"+classId+"'  GROUP BY job_id";
		try{
			studentList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		System.out.println("$$$$$$$$$$$$$$$$$$$"+studentList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object>  job_hard_overall_actual(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		String topicId=map.get("topicId").toString();
		System.out.println("&&&&&&&&&&&&"+choice1);
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		System.out.println("hahahahahhahaha"+courseId);
		String classId=session.getAttribute("CLASSID").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1.equals("") || choice1==null){
			sql = "select student_name  from job_is_hard,student  "+
					   "  where job_is_hard.student_id=student.student_id  "+
					   "  and topic_id='"+topicId+"' and course_id='"+courseId+"' and class_id='"+classId+"'   "+
					   "   and job_id=(select job_id from job_is_hard  where course_id='"+courseId+"'   and class_id='"+classId+"'   "+
					   "   order by job_id desc  limit 1)";
		
		}else{
			sql = "select student_name  from job_is_hard,student  "+
					   "  where job_is_hard.student_id=student.student_id  "+
					   "  and topic_id='"+topicId+"' and course_id='"+courseId+"' and class_id='"+classId+"'   "+
					   "   and job_id='"+choice1+"'  ";
		}
		
		System.out.println(sql);
		String  sql1 = "select job_id FROM job_is_hard where course_id='"+courseId+"' and class_id='"+classId+"'  GROUP BY job_id";
		try{
			studentList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		System.out.println("$$$$$$$$$$$$$$$$$$$"+studentList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object>  job_hard_overall_good_job(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		String topicId=map.get("topicId").toString();
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1.equals("") || choice1==null){
			sql = "select student_name FROM  "+
					 " (select topic_id,student_id,topic_name,topic_total_value,final_scorer,final_scorer/topic_total_value as rate from job_is_hard,topic  "+ 
					 "where job_is_hard.topic_id=topic.topic_only_id   "+
					 " and course_id='"+courseId+"'  and class_id='"+classId+"'  "+
					  " and topic_id='"+topicId+"' and job_id=(select job_id from job_is_hard   "+
					  " where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)  "+
					  " ORDER BY  topic_id) temp,student  "+
					  " where temp.student_id=student.student_id and  "+
					  " temp.topic_total_value=temp.final_scorer";
		
		}else{
			sql = "select student_name FROM  "+
					 " (select topic_id,student_id,topic_name,topic_total_value,final_scorer,final_scorer/topic_total_value as rate from job_is_hard,topic  "+ 
					 "where job_is_hard.topic_id=topic.topic_only_id   "+
					 " and course_id='"+courseId+"'  and class_id='"+classId+"'  "+
					  " and topic_id='"+topicId+"' and job_id='"+choice1+"'    "+
					  " ORDER BY  topic_id) temp,student  "+
					  " where temp.student_id=student.student_id and  "+
					  " temp.topic_total_value=temp.final_scorer";
		}
		try{
			studentList = dba.getMultiRecord(sql);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object>  job_hard_overall_bad_job(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		String topicId=map.get("topicId").toString();
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1.equals("") || choice1==null){
			sql = "select student_name FROM  "+
					 " (select topic_id,student_id,topic_name,topic_total_value,final_scorer,final_scorer/topic_total_value as rate from job_is_hard,topic  "+ 
					 "where job_is_hard.topic_id=topic.topic_only_id   "+
					 " and course_id='"+courseId+"'  and class_id='"+classId+"'  "+
					  " and topic_id='"+topicId+"' and job_id=(select job_id from job_is_hard   "+
					  " where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)  "+
					  " ORDER BY  topic_id) temp,student  "+
					  " where temp.student_id=student.student_id and  "+
					  " temp.final_scorer=0";
		
		}else{
			sql = "select student_name FROM  "+
					 " (select topic_id,student_id,topic_name,topic_total_value,final_scorer,final_scorer/topic_total_value as rate from job_is_hard,topic  "+ 
					 "where job_is_hard.topic_id=topic.topic_only_id   "+
					 " and course_id='"+courseId+"'  and class_id='"+classId+"'  "+
					  " and topic_id='"+topicId+"' and job_id='"+choice1+"'    "+
					  " ORDER BY  topic_id) temp,student  "+
					  " where temp.student_id=student.student_id and  "+
					  " temp.final_scorer=0";
		}
		try{
			studentList = dba.getMultiRecord(sql);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object> job_hard_overall_usual_rate(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		System.out.println("&&&&&&&&&&&&"+choice1);
		String topicId=map.get("topicId").toString();
		System.out.println("&&&&&&&&&&&&"+topicId);
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1=="" || choice1.equals("job_01")){
			sql="select student_name from"+
					  "(select topic_id,student_id,topic_name,topic_total_value,  "+
					     "final_scorer,final_scorer/topic_total_value as rate    "+
					     " from job_is_hard,topic   "+
					     "where job_is_hard.topic_id=topic.topic_only_id  "+
					     " and course_id='jsj_01' and job_id='job_01'  and topic_id='"+topicId+"'  "+
					     "ORDER BY  topic_id) temp,student  "+
					     "where temp.student_id=student.student_id  and rate<0.8 and rate>=0.6";
		}
		else{
		sql = "select student_name from"+
				  "(select topic_id,student_id,topic_name,topic_total_value,  "+
				     "final_scorer,final_scorer/topic_total_value as rate    "+
				     " from job_is_hard,topic   "+
				     "where job_is_hard.topic_id=topic.topic_only_id  "+
				     " and course_id='jsj_01' and job_id='"+choice1+"'  and topic_id='"+topicId+"'  "+
				     "ORDER BY  topic_id) temp,student  "+
				     "where temp.student_id=student.student_id  and rate<0.8 and rate>=0.6";
		}
		System.out.println(sql);
		String  sql1 = "select job_id FROM job_is_hard where course_id='jsj_01' GROUP BY job_id";
		try{
			studentList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		System.out.println("$$$$$$$$$$$$$$$$$$$"+studentList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object> job_hard_overall_fail_rate(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		System.out.println("&&&&&&&&&&&&"+choice1);
		String topicId=map.get("topicId").toString();
		System.out.println("&&&&&&&&&&&&"+topicId);
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1=="" || choice1.equals("job_01")){
			sql="select student_name from"+
					  "(select topic_id,student_id,topic_name,topic_total_value,  "+
					     "final_scorer,final_scorer/topic_total_value as rate    "+
					     " from job_is_hard,topic   "+
					     "where job_is_hard.topic_id=topic.topic_only_id  "+
					     " and course_id='jsj_01' and job_id='job_01'  and topic_id='"+topicId+"'  "+
					     "ORDER BY  topic_id) temp,student  "+
					     "where temp.student_id=student.student_id  and rate<0.6 ";
		}
		else{
		sql = "select student_name from"+
				  "(select topic_id,student_id,topic_name,topic_total_value,  "+
				     "final_scorer,final_scorer/topic_total_value as rate    "+
				     " from job_is_hard,topic   "+
				     "where job_is_hard.topic_id=topic.topic_only_id  "+
				     " and course_id='jsj_01' and job_id='"+choice1+"'  and topic_id='"+topicId+"'  "+
				     "ORDER BY  topic_id) temp,student  "+
				     "where temp.student_id=student.student_id  and rate<0.6";
		}
		System.out.println(sql);
		String  sql1 = "select job_id FROM job_is_hard where course_id='jsj_01' GROUP BY job_id";
		try{
			studentList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		System.out.println("$$$$$$$$$$$$$$$$$$$"+studentList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object> job_hard_overall_rate(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		System.out.println("&&&&&&&&&&&&"+choice1);
		String topicId=map.get("topicId").toString();
		System.out.println("&&&&&&&&&&&&"+topicId);
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentList = null;
		List<Map<String, String>> allJob = null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1=="" || choice1.equals("job_01")){
			sql="select student_name from"+
					  "(select topic_id,student_id,topic_name,topic_total_value,  "+
					     "final_scorer,final_scorer/topic_total_value as rate    "+
					     " from job_is_hard,topic   "+
					     "where job_is_hard.topic_id=topic.topic_only_id  "+
					     " and course_id='jsj_01' and job_id='job_01'  and topic_id='"+topicId+"'  "+
					     "ORDER BY  topic_id) temp,student  "+
					     "where temp.student_id=student.student_id  and rate>=0.8 ";
		}
		else{
		sql = "select student_name from"+
				  "(select topic_id,student_id,topic_name,topic_total_value,  "+
				     "final_scorer,final_scorer/topic_total_value as rate    "+
				     " from job_is_hard,topic   "+
				     "where job_is_hard.topic_id=topic.topic_only_id  "+
				     " and course_id='jsj_01' and job_id='"+choice1+"'  and topic_id='"+topicId+"'  "+
				     "ORDER BY  topic_id) temp,student  "+
				     "where temp.student_id=student.student_id  and rate>=0.8 ";
		}
		System.out.println(sql);
		String  sql1 = "select job_id FROM job_is_hard where course_id='jsj_01' GROUP BY job_id";
		try{
			studentList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  studentList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTLIST",  studentList);
		System.out.println("$$$$$$$$$$$$$$$$$$$"+studentList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	
	public Map<String, Object> job_hard_detail(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString()==null?null:map.get("jobId").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>> jobList = null;
		List<Map<String, String>> allJob = null;
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1=="" ){
			sql = "select topic_total_value  as  everyValue,topic_id,topic_name,avg(final_scorer) as avgScorer,max(final_scorer) as maxScorer,min(final_scorer) as minScorer   "+
						"  FROM(   "+
						"   SELECT * FROM   "+
						 "  (select * from job_is_hard,topic   "+ 
						 "  where job_is_hard.topic_id=topic.topic_only_id  "+
						 "  and course_id='"+courseId+"' and class_id='"+classId+"'   "+
						 "   and job_id=(select job_id from job_is_hard   "+
					     "   where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)   "+
						 "   ) no1   "+
						 "  ORDER BY topic_id) average  GROUP BY topic_id";
		}
		else{
			sql = "select topic_total_value  as  everyValue,topic_id,topic_name,avg(final_scorer) as avgScorer,max(final_scorer) as maxScorer,min(final_scorer) as minScorer   "+
					"  FROM(   "+
					"   SELECT * FROM   "+
					 "  (select * from job_is_hard,topic   "+ 
					 "  where job_is_hard.topic_id=topic.topic_only_id  "+
					 "  and course_id='"+courseId+"' and class_id='"+classId+"'   "+
					 "   and job_id='"+choice1+"'   "+
					 "   ) no1   "+
					 "  ORDER BY topic_id) average  GROUP BY topic_id";
		}
		String  sql1 = "select job_id FROM job_is_hard where course_id='"+courseId+"'  and class_id='"+classId+"' GROUP BY job_id";
		try{
			jobList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null == jobList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("JOBHARDLIST", jobList);
		resultMap.put("ALLJOB", allJob);
		
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	public Map<String, Object> job_hard_basic_data(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		String studentName=map.get("studentName").toString().replace(" ", "");
		String topicId=map.get("topicId").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  jobHardDataList = null;
		List<Map<String, String>> allJob = null;
		List<Map<String, String>> allTopic = null;
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		String sql=null;
		String topicSql = null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1=="" ){
			sql = "SELECT course_name,job_id,topic_no,topic_abstract,student.student_id,student_name, final_scorer    "+
						" from  student,job_is_hard,topic,course   "+
						" where student.student_id=job_is_hard.student_id  "+
						" and job_is_hard.topic_id=topic.topic_only_id   "+
						" and topic.belong_course_id=course.course_id  "+
						" and job_is_hard.course_id='"+courseId+"'    "+
						" and job_is_hard.job_id=(select job_id from job_is_hard   "+
					    " where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)   "+
					    " and job_is_hard.class_id='"+classId+"'    ";
		}
		else{
			sql =  "SELECT course_name,job_id,topic_no,topic_abstract,student.student_id,student_name, final_scorer    "+
					" from  student,job_is_hard,topic,course   "+
					" where student.student_id=job_is_hard.student_id  "+
					" and job_is_hard.topic_id=topic.topic_only_id   "+
					" and topic.belong_course_id=course.course_id  "+
					" and job_is_hard.course_id='"+courseId+"'    "+
					" and job_is_hard.job_id='"+choice1+"'     "+
				    " and job_is_hard.class_id='"+classId+"'    ";
		}
		if(!(topicId.equals("")) && topicId!=null){
			sql=sql+"  "+" and job_is_hard.topic_id='"+topicId+"'  ";
		}
		if(!(studentName.equals("")) && studentName!=null){
			sql = sql+"  " +"     and student.student_name like '%"+studentName+"%'   ";
		}
		sql = sql+"   "   +"   order by topic_no,student_id   ";
		String  jobSql= "select job_id FROM job_is_hard where course_id='"+courseId+"' and class_id='"+classId+"' GROUP BY job_id";
		if(choice1.equals("") || choice1==null){
			topicSql = "select DISTINCT topic_only_id,topic_abstract from topic  "+
								" where belong_course_id='"+courseId+"'   "+
								"  and belong_job_id=(select job_id from job_is_hard   where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)  ";  
		}else{
			topicSql =  "select DISTINCT topic_only_id,topic_abstract from topic  "+
					" where belong_course_id='"+courseId+"'   "+
					"  and belong_job_id='"+choice1+"' ";  
		}
		try{
			 jobHardDataList = dba.getMultiRecord(sql);
			 allJob = dba.getMultiRecord(jobSql);
			 allTopic = dba.getMultiRecord(topicSql);
			 dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  jobHardDataList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("JOBHARDDATA",  jobHardDataList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("ALLTOPIC", allTopic);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}

	
	
	public Map<String, Object> 	 job_hard_data_topic(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>> allTopic = null;
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		String topicSql = null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1.equals("") || choice1==null){
			topicSql = "select DISTINCT topic_only_id,topic_abstract from topic  "+
								" where belong_course_id='"+courseId+"'   "+
								"  and belong_job_id=(select job_id from job_is_hard   where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)  ";  
		}else{
			topicSql =  "select DISTINCT topic_only_id,topic_abstract from topic  "+
					" where belong_course_id='"+courseId+"'   "+
					"  and belong_job_id='"+choice1+"' ";  
		}
		try{
			 allTopic = dba.getMultiRecord(topicSql);
			 dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==  allTopic){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		System.out.println(topicSql);
		System.out.println(allTopic);
		resultMap.put("ALLTOPIC", allTopic);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}

}
